package com.guldan.thrall.util;

import com.alibaba.fastjson.JSON;
import com.guldan.core.enums.CitizenUpdateTypeEn;
import com.guldan.core.enums.CitizenValidatingTypeEn;
import com.guldan.core.enums.ValidatingCitizenUpdateTypeEn;
import com.guldan.core.exception.BusinessException;
import com.guldan.core.utils.MathUtil;
import com.guldan.jaina.dto.citizenbatchupdate.CitizenBatchUpdateDTO;
import com.guldan.core.constants.XlsHeaderConstant;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Silas.
 * Date: 2016/12/26
 * Time: 21:58
 */
public class CitizenUpdateExcel {

    public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
    public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";

    public static final String EMPTY = "";
    public static final String POINT = ".";
    public static final String NOT_EXCEL_FILE = " : Not the Excel file!";
    public static final String PROCESSING = "Processing...";

    private static int type;   //1:更新名字 2:更新卡号

    /**
     * read the Excel file
     *
     * @param path the path of the Excel file
     * @return
     * @throws IOException
     */

    public CitizenUpdateExcel() {

    }

    public CitizenUpdateExcel(int type) {
        this.type = type;
    }

    public List<CitizenBatchUpdateDTO> readExcel(String path) throws IOException {
        if (path == null || EMPTY.equals(path)) {
            return null;
        } else {
            String postfix = getPostfix(path);
            if (!EMPTY.equals(postfix)) {
                return readXls(path);
            } else {
                System.out.println(path + NOT_EXCEL_FILE);
            }
        }
        return null;
    }


    /**
     * Read the Excel 2003-2007
     *
     * @param path the path of the Excel
     * @return
     * @throws IOException
     */
    //public List<RegisterTemplateDataDTO> readXls(String path) throws IOException {
    public List<CitizenBatchUpdateDTO> readXls(String path) throws IOException, BusinessException {
        System.out.println(PROCESSING + path);
        InputStream is = new FileInputStream(path);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        List<CitizenBatchUpdateDTO> list = new ArrayList<CitizenBatchUpdateDTO>();
        // Read the Sheet
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {

            if (numSheet == 1) {
                //只读取第一个表的信息
                break;
            }

            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }

            if (hssfSheet.getPhysicalNumberOfRows() < 3) {
                throw new BusinessException(ValidatingCitizenUpdateTypeEn.notFindRecord.getMean()
                        , ValidatingCitizenUpdateTypeEn.notFindRecord.getCode().toString());
            }

            // Read the Row，从第三行开始
            for (int rowNum = 2; rowNum <= hssfSheet.getPhysicalNumberOfRows(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                    CitizenBatchUpdateDTO dataDto = new CitizenBatchUpdateDTO();

                    if (isEndRow(hssfRow)) {
                        break;
                    }

                    String identNumber = "", oriName = "", name = "", bankName = "", bankNumber = "", newAreaCode = "";
                    if (this.type == CitizenUpdateTypeEn.citizen_name.getCode().intValue()) {
                        //更新姓名
                        identNumber = getStringCellValue(hssfRow.getCell(0));
                        if (StringUtils.isBlank(identNumber)) {
                            throw new BusinessException(String.format(ValidatingCitizenUpdateTypeEn.infoCanNotNull.getMean()
                                    , rowNum - 1
                                    , XlsHeaderConstant.CITIZEN_UPDATE_NAME_XLS_HEADERS[0])
                                    , ValidatingCitizenUpdateTypeEn.infoCanNotNull.getCode().toString());
                        }

                        oriName = getStringCellValue(hssfRow.getCell(1));
                        name = getStringCellValue(hssfRow.getCell(2));

                        if (StringUtils.isBlank(name)) {
                            throw new BusinessException(String.format(ValidatingCitizenUpdateTypeEn.infoCanNotNull.getMean()
                                    , rowNum - 1
                                    , XlsHeaderConstant.CITIZEN_UPDATE_NAME_XLS_HEADERS[2])
                                    , ValidatingCitizenUpdateTypeEn.infoCanNotNull.getCode().toString());
                        }
                    } else if (this.type == CitizenUpdateTypeEn.citizen_bank.getCode().intValue()) {
                        //更新卡号
                        name = getStringCellValue(hssfRow.getCell(0));
                        identNumber = getStringCellValue(hssfRow.getCell(1));
                        if (StringUtils.isBlank(identNumber)) {
                            throw new BusinessException(String.format(ValidatingCitizenUpdateTypeEn.infoCanNotNull.getMean()
                                    , rowNum - 1
                                    , XlsHeaderConstant.CITIZEN_UPDATE_BANK_XLS_HEADERS[1])
                                    , ValidatingCitizenUpdateTypeEn.infoCanNotNull.getCode().toString());
                        }
                        bankName = getStringCellValue(hssfRow.getCell(2));
                        if (StringUtils.isBlank(bankName)) {
                            throw new BusinessException(String.format(ValidatingCitizenUpdateTypeEn.infoCanNotNull.getMean()
                                    , rowNum - 1
                                    , XlsHeaderConstant.CITIZEN_UPDATE_BANK_XLS_HEADERS[2])
                                    , ValidatingCitizenUpdateTypeEn.infoCanNotNull.getCode().toString());
                        }
                        bankNumber = getStringCellValue(hssfRow.getCell(3));
                        if (StringUtils.isBlank(bankNumber)) {
                            throw new BusinessException(String.format(ValidatingCitizenUpdateTypeEn.infoCanNotNull.getMean()
                                    , rowNum - 1
                                    , XlsHeaderConstant.CITIZEN_UPDATE_BANK_XLS_HEADERS[3])
                                    , ValidatingCitizenUpdateTypeEn.infoCanNotNull.getCode().toString());
                        } else {
                            //校验是否为数字
                            if (!MathUtil.isInteger(bankNumber)) {
                                throw new BusinessException(String.format(ValidatingCitizenUpdateTypeEn.formatErrorByRow.getMean()
                                        , rowNum - 1
                                        , XlsHeaderConstant.CITIZEN_UPDATE_BANK_XLS_HEADERS[3])
                                        , ValidatingCitizenUpdateTypeEn.formatErrorByRow.getCode().toString());
                            }
                        }
                    } else if (this.type == CitizenUpdateTypeEn.citizen_area.getCode().intValue()) {
                        //更新区划

                        identNumber = getStringCellValue(hssfRow.getCell(0));
                        if (StringUtils.isBlank(identNumber)) {
                            throw new BusinessException(String.format(ValidatingCitizenUpdateTypeEn.infoCanNotNull.getMean()
                                    , rowNum - 1
                                    , XlsHeaderConstant.CITIZEN_UPDATE_AREA_XLS_HEADERS[0])
                                    , ValidatingCitizenUpdateTypeEn.infoCanNotNull.getCode().toString());
                        }
                        newAreaCode = getStringCellValue(hssfRow.getCell(1));
                        if (StringUtils.isBlank(newAreaCode)) {
                            throw new BusinessException(String.format(ValidatingCitizenUpdateTypeEn.infoCanNotNull.getMean()
                                    , rowNum - 1
                                    , XlsHeaderConstant.CITIZEN_UPDATE_AREA_XLS_HEADERS[1])
                                    , ValidatingCitizenUpdateTypeEn.infoCanNotNull.getCode().toString());
                        } else {
                            if (!MathUtil.isInteger(newAreaCode)) {
                                throw new BusinessException(String.format(ValidatingCitizenUpdateTypeEn.formatErrorByRow.getMean()
                                        , rowNum - 1
                                        , XlsHeaderConstant.CITIZEN_UPDATE_AREA_XLS_HEADERS[1])
                                        , ValidatingCitizenUpdateTypeEn.formatErrorByRow.getCode().toString());
                            }
                        }
                    }

                    dataDto.setIdentNumber(identNumber);
                    dataDto.setOriName(oriName);
                    dataDto.setName(name);
                    dataDto.setBankName(bankName);
                    dataDto.setBankNumber(bankNumber);
                    dataDto.setNewAreaCode(newAreaCode);

                    list.add(dataDto);
                }
            }
        }
        return list;
    }

    private String getStringCellValue(HSSFCell cell) {
        if (cell != null) {
            cell.setCellType(Cell.CELL_TYPE_STRING);
            return cell.getStringCellValue();
        }
        return null;
    }

    private Boolean getBooleanCellValue(HSSFCell cell) {
        if (cell != null) {
            cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
            return cell.getBooleanCellValue();
        }
        return null;
    }

    @SuppressWarnings("static-access")
    private String getValue(XSSFCell xssfCell) {
        if (xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(xssfCell.getBooleanCellValue());
        } else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {
            return String.valueOf(xssfCell.getNumericCellValue());
        } else {
            return String.valueOf(xssfCell.getStringCellValue());
        }
    }

    @SuppressWarnings("static-access")
    private String getValue(HSSFCell hssfCell) {
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
            return String.valueOf(hssfCell.getNumericCellValue());
        } else {
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }

    private Boolean isEndRow(HSSFRow hssfRow) {
        boolean ret = true;
        int total = hssfRow.getPhysicalNumberOfCells();
        for (int i = 0; i < total; i++) {
            if (StringUtils.isNotBlank(getStringCellValue(hssfRow.getCell(i)))) {
                ret = false;
                break;
            }
        }
        return ret;
    }

    public static void main(String[] args) {
        CitizenUpdateExcel excel = new CitizenUpdateExcel(2);
        String filePath = "F:\\2222.xls";
        try {
            List<CitizenBatchUpdateDTO> list = excel.readExcel(filePath);

            if (list != null)
                System.out.println(JSON.toJSONString(list));
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (BusinessException e) {
            System.out.println(e.getMessage());
        }
    }

    public String getPostfix(String path) {
        if (path == null || EMPTY.equals(path.trim())) {
            return EMPTY;
        }
        if (path.contains(POINT)) {
            return path.substring(path.lastIndexOf(POINT) + 1, path.length());
        }
        return EMPTY;
    }

}
